using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Text;
using System.Text.RegularExpressions;
using System.Web;
using System.Web.Configuration;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Xml;
using System.Xml.Serialization;

public partial class AdHocSqlForm : System.Web.UI.Page
{
	private AdHocSql _oAdHocSql;
	private Regex _isGuid = new Regex(@"^(\{){0,1}[0-9a-fA-F]{8}\-[0-9a-fA-F]{4}\-[0-9a-fA-F]{4}\-[0-9a-fA-F]{4}\-[0-9a-fA-F]{12}(\}){0,1}$", RegexOptions.Compiled);
	private bool _isSubmit = false;
	private string _sql;

    protected void Page_Load(object sender, EventArgs e)
    {
		try
		{
			// Load Xml data
			XmlReader xr = XmlReader.Create(Server.MapPath("App_Data\\AdHocSql.xml"));
			XmlDocument xd = new XmlDocument();
			xd.Load(xr);
			XmlNodeReader xnr = new XmlNodeReader(xd.DocumentElement);
			XmlSerializer xs = new XmlSerializer(typeof(AdHocSql));
			_oAdHocSql = (AdHocSql)xs.Deserialize(xnr);

			if (!IsPostBack)
			{
				// Create a meta tag that expires in 19.5 minutes. It expires before the session variables and forms authentication cookies.
				try
				{
					Control ctrl = Page.Header;
					HtmlMeta meta = new HtmlMeta();
					meta.HttpEquiv = "refresh";
					meta.Content = String.Format("1170;url={0}", ResolveUrl("~/Logout.aspx"));
					ctrl.Controls.Add(meta);
				}
				catch
				{
					// I just don't want an error here to stop the application
				}
				// Load connections dropdownlist
				ddlConnection.DataSource = _oAdHocSql.Connections;
				// Insert Please select query
				_oAdHocSql.SqlStatements.Insert(0, new AdHocSqlStatement("Please select query...", ""));
				// Load queries dropdownlist
				ddlQuery.DataSource = _oAdHocSql.SqlStatements;

				// Bind data
				DataBind();
			}
			else
			{
				// Clear this flag
				_isSubmit = false;
			}
		}
		catch (Exception ex)
		{
			lblError.Text = ex.Message;
		}
    }

    protected void Page_PreRender(object sender, System.EventArgs e)
	{
		try
		{
			if (_isSubmit)
			{
				// Build Javascript 
				StringBuilder strScript = new StringBuilder();
				strScript.AppendLine("function OpenWindowForExport()");
				strScript.AppendFormat("{0}\n", "{");
				strScript.AppendFormat("    var sUrl = '{0}ExportData.aspx?conn={1}&sql={2}", this.ResolveUrl("~/"), ddlConnection.SelectedValue, ddlQuery.SelectedValue.Replace("'", "\\'"));
				// Append each form variable name and value to the request.
				for (int i = 0; i < _oAdHocSql.SqlStatements[ddlQuery.SelectedIndex - 1].Parameters.Count; i++)
				{
					// Add Logic
					if (i > 0)
						strScript.AppendFormat("&Logic{0}={1}", i, Server.UrlEncode(Request["Logic" + i.ToString()]));
					// Add Operator
					strScript.AppendFormat("&Operator{0}={1}", i, Server.UrlEncode(Request["Operator" + i.ToString()]));
					// Add Value
					string name = RemovePrefix(_oAdHocSql.SqlStatements[ddlQuery.SelectedIndex - 1].Parameters[i].Name);
					// Don't forget to escape apostrophes
					string fldValue = Request[name].Replace("'", "\\'");
					strScript.AppendFormat("&{0}={1}", name, Server.UrlEncode(fldValue));
				}
				strScript.AppendLine("';");
				strScript.AppendLine("    window.open(sUrl,\"ExportAll\",\"width=600,height=400,directories=no,location=yes,menubar=yes,resizable=yes,scrollbars=yes,status=yes,toolbar=no\");");
				strScript.AppendLine("    return false;");
				strScript.AppendFormat("{0}\n", "}");

				ClientScript.RegisterClientScriptBlock(typeof(Page), "ClientSideFunctions", strScript.ToString(), true);
			}
		}
		catch (Exception ex)
		{
			lblError.Text = ex.Message;
		}
	}

	protected void ddlQuery_SelectedIndexChanged(object sender, EventArgs e)
	{
        // Clear old results
        pnlParameters.Controls.Clear();
        pnlResults.Visible = false;
        // Display form
        DisplayForm();
	}

	protected void Button_Click(object sender, System.EventArgs e)
	{
		Button b;

		// Clear the error
		lblError.Text = "";
		// What was clicked?
		b = (Button)sender;
		switch (b.ID)
		{
			case "btnSubmit":
				if (ValidateFormInputs())
				{
					// Set the _isSubmit flag is we get results.
					_isSubmit = GetResults();
				}
				break;
			case "btnReset":
				pnlParameters.Controls.Clear();
				pnlResults.Visible = false;
				ddlQuery.SelectedIndex = 0;
				break;
			default:
				break;
		}
	}

	private void DisplayError(string msg)
	{
		lblError.Text = msg;
	}
	
	private void DisplayException(Exception ex)
	{
		StringBuilder sb = new StringBuilder();
		Exception innerEx = ex;
		while (innerEx != null)
		{
			sb.AppendFormat("{0}<br/>", innerEx.Message);
			innerEx = innerEx.InnerException;
		}
		lblError.Text = sb.ToString();
	}

	private void DisplayForm()
	{
		pnlParameters.Controls.Clear();
		if (ddlQuery.SelectedIndex > 0)
		{
			LiteralControl lt = new LiteralControl("<br/>");
			pnlParameters.Controls.Add(lt);
			Table tbl = new Table();
			TableRow tr;
			TableCell tc;
			Label lbl;
			DropDownList ddl;
			List<string> logic;
			List<string> ops;
			TextBox txt;
			AdHocSqlParameter parm;
			for (int i = 0; i < _oAdHocSql.SqlStatements[ddlQuery.SelectedIndex - 1].Parameters.Count; i++)
			{
				if (i > 0)
				{
					// Create a row
					tr = new TableRow();
					// Create a label field
					tc = new TableCell();
					tc.ColumnSpan = 2;
					ddl = new DropDownList();
					ddl.ID = "Logic" + i.ToString();
					logic = new List<string>();
					logic.Add("and");
					logic.Add("and not");
					logic.Add("or");
					logic.Add("or not");
					ddl.DataSource = logic;
					ddl.DataBind();
					tc.Controls.Add(ddl);
					tr.Cells.Add(tc);
					tbl.Rows.Add(tr);
				}
				// Get the current parameter
				parm = _oAdHocSql.SqlStatements[ddlQuery.SelectedIndex - 1].Parameters[i];
				// Create a row
				tr = new TableRow();
				// Create a label field
				tc = new TableCell();
				lbl = new Label();
				lbl.Text = parm.Label;
				tc.Controls.Add(lbl);
				tr.Cells.Add(tc);

				// Create operators field
				tc = new TableCell();
				ddl = new DropDownList();
				ddl.ID = "Operator" + i.ToString();
				ops = new List<string>();
				ops.Add(" = ");
				ops.Add(" != ");
				ops.Add(" < ");
				ops.Add(" <= ");
				ops.Add(" > ");
				ops.Add(" >= ");
				ops.Add(" is ");
				ops.Add(" is not ");
				ops.Add(" like ");
				ddl.DataSource = ops;
				ddl.DataBind();
				tc.Controls.Add(ddl);
				tr.Cells.Add(tc);

				// Create a value field
				tc = new TableCell();
				txt = new TextBox();
				txt.ID = RemovePrefix(parm.Name);
				tc.Controls.Add(txt);
				tr.Cells.Add(tc);
				// Add the row to the table
				tbl.Rows.Add(tr);
			}
			// Add the table to the panel
			tbl.CellPadding = 2;
			tbl.CellSpacing = 2;
			tbl.Width = 500;
			pnlParameters.Controls.Add(tbl);
		}
	}

	private bool GetResults()
	{
        string fldValue;
		string name = "";
		int orderby = 0;
		string sOrderBy = "";
		bool fSuccess = false;
		try
		{
			// Initialize the SQL String
			_sql = "";

			using (SqlConnection conn = new SqlConnection(WebConfigurationManager.ConnectionStrings[ddlConnection.SelectedValue].ConnectionString))
			{
				// If the statement a procedure
				bool isProc = false;
				if (_oAdHocSql.SqlStatements[ddlQuery.SelectedIndex - 1].Statement.ToLower().Contains("exec"))
					isProc = true;
				// Get the statement
				_sql = _oAdHocSql.SqlStatements[ddlQuery.SelectedIndex - 1].Statement;
				if (_sql.ToLower().Contains("{parameters}"))
				{
					orderby = _sql.ToLower().IndexOf("{parameters}");
					_sql = _sql.ToLower().Replace("{parameters}", "");
					if (_sql.Length > orderby)
					{
						sOrderBy = _sql.Substring(orderby);
						_sql = _sql.Substring(0, orderby - 1);
					}
				}
				if (!isProc && _oAdHocSql.SqlStatements[ddlQuery.SelectedIndex - 1].Parameters.Count > 0) _sql += " where ";
				// Loop through each parameter and append to sql statement
				for (int i = 0; i < _oAdHocSql.SqlStatements[ddlQuery.SelectedIndex - 1].Parameters.Count; i++)
				{
                    name = "@" + RemovePrefix(_oAdHocSql.SqlStatements[ddlQuery.SelectedIndex - 1].Parameters[i].Name);

                    if (!isProc)
					{
						if (i > 0)
							_sql += String.Format(" {0}", Request["Logic" + i.ToString()]);

                        // Get the request value
                        fldValue = Request[RemovePrefix(_oAdHocSql.SqlStatements[ddlQuery.SelectedIndex - 1].Parameters[i].Name)];
                        // If we are processing the 'is' or 'is not' operator, and the value is 'null'
                        if ((Request["operator" + i] == " is " || Request["operator" + i] == " is not ") && fldValue.Trim().ToLower() == "null")
                        {
                            // Change the syntax of the sql statement to accommodate is null or is not null
                            _sql += string.Format(" {0} {1} {2}", _oAdHocSql.SqlStatements[ddlQuery.SelectedIndex - 1].Parameters[i].Name, Request["Operator" + i], fldValue);
                        }
                        else
                        {
                            // Else parameterize the sql statement field and value
                            _sql += string.Format(" {0} {1} {2}", _oAdHocSql.SqlStatements[ddlQuery.SelectedIndex - 1].Parameters[i].Name, Request["Operator" + i], name);
                        }
					}
					else
					{
                        // Sql statement is a stored procedure
						if ( i > 0)
							_sql += ",";

                        _sql += string.Format(" {0}", name);
					}
				}
				if ( !string.IsNullOrEmpty(sOrderBy))
				{
					_sql += " " + sOrderBy;
				}
				// Open database
				conn.Open();
				// Create command object with SQL query like before, but with parameter instead of hard coding
				SqlCommand comm = new SqlCommand(_sql, conn);
				// Loop through each parameter and add to sql command
				for (int i = 0; i < _oAdHocSql.SqlStatements[ddlQuery.SelectedIndex - 1].Parameters.Count; i++)
				{
					name = "@" + RemovePrefix(_oAdHocSql.SqlStatements[ddlQuery.SelectedIndex - 1].Parameters[i].Name);

                    // Get the request value
                    fldValue = Request[RemovePrefix(_oAdHocSql.SqlStatements[ddlQuery.SelectedIndex - 1].Parameters[i].Name)];
                    // If we are not processing the 'is' and 'is not' operator, or the value is not 'null'
                    if ((Request["operator" + i] != " is " && Request["operator" + i] != " is not ") || fldValue.Trim().ToLower() != "null")
                    {
                        // Create a parameter and convert the request value
                        switch (_oAdHocSql.SqlStatements[ddlQuery.SelectedIndex - 1].Parameters[i].DataType.ToLower())
                        {
                            case "int":
                                comm.Parameters.Add(name, SqlDbType.Int);
                                comm.Parameters[name].Value = Convert.ToInt32(fldValue);
                                break;
                            case "string":
                                comm.Parameters.Add(name, SqlDbType.NVarChar);
                                comm.Parameters[name].Value = fldValue;
                                break;
                            case "double":
                                comm.Parameters.Add(name, SqlDbType.Decimal);
                                comm.Parameters[name].Value = Convert.ToDecimal(fldValue);
                                break;
                            case "date":
                                comm.Parameters.Add(name, SqlDbType.DateTime);
                                comm.Parameters[name].Value = Convert.ToDateTime(fldValue);
                                break;
                            case "uniqueid":
                                comm.Parameters.Add(name, SqlDbType.NVarChar);
                                comm.Parameters[name].Value = Convert.ToString(fldValue);
                                break;
                            default:
                                break;
                        }
                    }
				}
				DataSet ds = new DataSet();
				SqlDataAdapter da = new SqlDataAdapter(comm);
				da.Fill(ds);
				// Use data on some way... 
				if (ds != null && ds.Tables.Count > 0)
				{
					if (ds.Tables[0].Rows.Count == 0)
					{
						lblError.Text = "No results returned";
					}
					else
					{
						gvResults.DataSource = ds.Tables[0];
						gvResults.DataBind();
						pnlResults.Visible = true;
						btnToCsv.Visible = true;
						fSuccess = true;
					}
				}
				else
				{
					lblError.Text = "No results returned";
				}
				// Close connection
				conn.Close();
			}
		}
		catch (Exception ex)
		{
			DisplayException(ex);
		}
		if (!fSuccess)
			DisplayForm();

		return fSuccess;
	}

	private bool IsGuid(string candidate)
	{
		bool fIsGuid = _isGuid.IsMatch(candidate);
		if (candidate.StartsWith("{"))
			if (!candidate.EndsWith("}"))
				fIsGuid = false;
		return fIsGuid;
	}

    private string RemovePrefix(string name)
    {
        string newName = name;
        int offset = name.IndexOf(".");
        if (offset >= 0)
            newName = name.Substring(offset + 1);

        return newName;
    }

	private bool ValidateFormInputs()
	{
		string fldName;
		string fldType;
		string fldValue;
		int intValue;
		double dblValue;
		DateTime dtValue;

		bool fSuccess = false;
		try
		{
			// Loop through each parameter and validate the input data
			for (int i = 0; i < _oAdHocSql.SqlStatements[ddlQuery.SelectedIndex - 1].Parameters.Count; i++)
			{
				fldName = _oAdHocSql.SqlStatements[ddlQuery.SelectedIndex - 1].Parameters[i].Name;
				fldType = _oAdHocSql.SqlStatements[ddlQuery.SelectedIndex - 1].Parameters[i].DataType.ToLower();
				fldValue = Request[RemovePrefix(_oAdHocSql.SqlStatements[ddlQuery.SelectedIndex - 1].Parameters[i].Name)];
				if (string.IsNullOrEmpty(fldValue))
				{
					DisplayError(String.Format("{0} has a null value", fldName));
					return fSuccess;
				}
                // If we are not processing the 'is' and 'is not' operator, or the value is not 'null'
                fldValue = fldValue.Trim().ToLower();
                if (Request["operator" + i] != " is " && Request["operator" + i] != " is not ")
                {
                    switch (fldType)
                    {
                        case "int":
                            if (!Int32.TryParse(fldValue, out intValue))
                            {
                                DisplayError(String.Format("{0} has an invalid integer value", fldName));
                                return false;
                            }
                            break;

                        case "string":
                            break;

                        case "double":
                            if (!Double.TryParse(fldValue, out dblValue))
                            {
                                DisplayError(String.Format("{0} has an invalid double value", fldName));
                                return false;
                            }
                            break;

                        case "date":
                            if (!DateTime.TryParse(fldValue, out dtValue))
                            {
                                DisplayError(String.Format("{0} has an invalid date value", fldName));
                                return false;
                            }
                            break;

                        case "uniqueid":
                            if (!IsGuid(fldValue))
                            {
                                DisplayError(String.Format("{0} has an invalid uniqueid value", fldName));
                                return false;
                            }
                            break;

                        default:
                            DisplayError("Invalid data type");
                            return fSuccess;
                    }
                }
                else if (fldValue != "null")
                {
                    DisplayError("The 'is' and 'is not' operators must have a value of 'null'");
                    return fSuccess;
                }
			}
			fSuccess = true;
		}
		catch (Exception ex)
		{
			DisplayException(ex);
		}
		return fSuccess;
	}
}
